package System;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;
import System.SystemObject;
import System.EchoServer;
import BaseClasses.PracticalExam;
import BaseClasses.Question;
import BaseClasses.Subject;
import BaseClasses.User;
import BaseClasses.isRestricted;
import System.SysMode;



/**
 * This class connect to the database
 * and performs the requested queries
 * 
 * @author Baruch Cohen 
 * @author Nir Bazak
 * @author Gil klebanov
 * @author Ivgeny kristhul
 * @version June 2012
 * 
 */


public class DataBase 
{
	private Connection conn;
	private static final String database = "jdbc:mysql://localhost/project";
	private static final String driver = "com.mysql.jdbc.Driver";
	//**UserName for mySQL **//
	private static final String userName = "root"; 
	//**Password for mySQL **//
	private static String passWord;
	
	//**Constructors**//
	/**
	 * 
	 * @param pass Database password
	 */
	public DataBase(String pass) throws Exception
	{
		passWord = pass;
		//**use the driver of SQL**//
		java.lang.Class.forName(driver).newInstance();
	}
	/**
	 * This method set the connection to the DB
	 */
	public void setConnection() throws Exception
	{
		conn = DriverManager.getConnection(database,userName,passWord);          
	}
	/**
	 * This method close the connection to the DB
	 */
	public void closeConnection() throws Exception
	{
		conn.close();
	}
	
	private void setPreparedStatement(PreparedStatement pt,int index,Object obj) throws Exception
	{
		if (obj instanceof String)  {pt.setString(index, (String)obj); return;}
		if (obj instanceof Integer) {pt.setInt(index, (Integer)obj); return;}
		if (obj instanceof Float)	{pt.setFloat(index, (Float)obj);	return;}
		if (obj instanceof Date)	{pt.setDate( index, new java.sql.Date( ( (Date)obj ).getTime() ) ); return;}
		if (obj instanceof Time)	{pt.setTime(index, (Time)obj); return;}
		throw new Exception("Wrong Type of Values, Please check with system Admin");
	}
	
	
	/**
	 * This is a generic method will execute a query or an update and returns the result
	 * @param obj - an object[] holding the parameters for query
	 * 				obj[0] - the prepared statement's query (SELECT..FROM..WHERE..=? AND ...)
	 * 				obj[1]..obj[N] - the variables for the query - (?,?,?..)
	 * @return a system Object with the result and mode OK or an error message if mode is NOT_OK.
	 */

	public SystemObject sysCheck(Object[] obj)
	{
		//**Instance of PreparedStatement**//
		PreparedStatement pt;
		int arrSize = obj.length;
		int ptIndex, arrIndex;
		//**Obj[0] will contain the Query msg**//
		String Query = (String) obj[0];
		int res;
		try
		{
			//**Use prepareStatement **//
			pt = conn.prepareStatement(Query);
			//**Fill array with Values from Query**//
			for(arrIndex = 1, ptIndex = 1; arrIndex < arrSize; arrIndex++, ptIndex++ )
				setPreparedStatement(pt, ptIndex, obj[arrIndex]);
			//**use Result Set AS output of the Query**//
			 ResultSet rs =  pt.executeQuery();
			 if(rs.next() == true)
				 res=1;
			 else
				 res=0;
			 return (new SystemObject(res,SysMode.OK));
		}catch(Exception ex) {return new SystemObject(ex.getMessage(),SysMode.NOT_OK);}
	}		
	
	public Object getLogin(User usr)
	{
		Object[] result = new Object[2];
		User dbUsr;
		PreparedStatement prps;
		String Query2 = "UPDATE users SET isOnline = 1 where UserID = ? AND isOnline = 0";	
		String Query = "SELECT * from users where UserID = ?";
		ResultSet rs;
		try {
			prps = conn.prepareStatement(Query);
			prps.setString(1, usr.getUserID());
			rs = prps.executeQuery();
			if (rs.next())
			{
				dbUsr = new User();
				dbUsr.setUsrName(rs.getString(1));
				dbUsr.setPassword(rs.getString(2));
				dbUsr.setFirstName(rs.getString(3));
				dbUsr.setLastName(rs.getString(4));
				dbUsr.setRole(rs.getInt(5));
				dbUsr.setUsrId(rs.getString(6));
				
				if(usr.getPassword().equals(dbUsr.getPassword()))
				{
					prps = conn.prepareStatement(Query2);
					prps.setString(1, dbUsr.getUserID());
					if (0 == prps.executeUpdate() )
						result[0] = new String("Youre User Id is logged in other computer, Log out first");
					else
						result[0] = dbUsr;
					return (result);
				}
				else
					return null;
			}
			else return null;

		} catch (SQLException e) {EchoServer.display("Method getLogin: "+e.getMessage());}
		return null;
	}

	
	/*class handling methods*/
	public Object getSubjectbySid(Object[] obj) 
	{
		Object[] Question = new Object[1];
		PreparedStatement prps;
		ResultSet rs;
		String Query = "INSERT code from subject where Sid=?";

		try {
			prps = conn.prepareStatement(Query);
			prps.setString(1, obj[0].toString());
			rs = prps.executeQuery();
			if(rs.next())
			{
				Question[0] = rs.getString(1);				
			}
			return Question;
		} catch (SQLException e) 
		{
			EchoServer.display("get Subject By Sid: "+e.getMessage());
		}

		return null;
	}
	public int insertQuestion(Question quest)
	{
		
		PreparedStatement pt;
		
		int rs;
		String Query = "INSERT INTO questions (id,text,first_answer,second_answer,third_answer,fourth_answer,correctAnswer,subq) Values ";
		Query+= "('";
		Query+=(quest.getID() + "','");
		Query+=(quest.getText() + "','");
		Query+=(quest.getfirst() + "','");
		Query+=(quest.getsecond()+ "','");
		Query+=(quest.getthird()+ "','");
		Query+=(quest.getfourth()+ "','");
		//Query+=(quest.getNumber()+ "','");
		Query+=(quest.getCorrectAnswer()+ "','"); 
		Query+=(quest.getSubject()+ "')");
		
		System.out.println(Query);
		try
		{
			pt = conn.prepareStatement(Query);
			rs=pt.executeUpdate();
			return rs;

		}
	 catch (SQLException e) {
		 EchoServer.display("Method InsertQuestion: "+e.getMessage());
		 }
	 return 0;
}

	public Object DeleteQuestion(Question quest)
	{
		
		PreparedStatement pstmt;
		int rs;
		String Query = "DELETE FROM questions WHERE id =?";
		//SystemObject result = new SystemObject();
		try
		{
			pstmt = conn.prepareStatement(Query);
			pstmt.setString(1,quest.getID());
			rs = pstmt.executeUpdate();
			return rs;			
		}
		catch(Exception ex)
		{
			return (new SystemObject(new String("Exception, Question ID Doesn't Exist,/ntry Again"),SysMode.FAIL));
		}
	}
	
	public Object DeleteExam(PracticalExam exam)
	{
		PreparedStatement pstmt;
		int rs;
		String Query = "DELETE FROM project.practicalexam WHERE examID =?";
		try
		{
			pstmt = conn.prepareStatement(Query);
			pstmt.setString(1,exam.getExamID());
			rs = pstmt.executeUpdate();
			return rs;			
		}
		catch(Exception ex)
		{
			return (new SystemObject(new String("Exception, Exam ID Doesn't Exist,/ntry Again"),SysMode.FAIL));
		}
	}
	
	public SystemObject getQuestions()
	{
		List<Question> list;
		Question q;
		Statement st;
		String Query = "SELECT * from questions";
		ResultSet rs;
		try {
			list = new ArrayList<Question>();
			st = conn.createStatement();
			rs = st.executeQuery(Query);
			while (rs.next())
			{
				q = new Question();
				q.setId(rs.getString(1));
				q.setText(rs.getString(2));
				q.setfirst(rs.getString(3));
				q.setsecond(rs.getString(4));
				q.setthird(rs.getString(5));
				q.setfourth(rs.getString(6));
				q.setCorrectAnswer(rs.getString(7));
				list.add(q);
			}
			//result[0] = list;
			SystemObject obj = new SystemObject();
			obj.setMode(SysMode.OK);
			obj.setObj(list);
			return obj;

		} catch (SQLException e) {EchoServer.display("Method getLogin: "+e.getMessage());}
		return null;
	}
	

	public SystemObject getQuestionsbysid(Subject s)
	{
		List<Question> list;
		Question q;
		PreparedStatement stmt;
		ResultSet rs;
		String Query = "SELECT q.text, q.id From questions q ,teachersubject t  WHERE t.subjectcode=q.subq and t.name=?";
		
		try {
			stmt = conn.prepareStatement(Query);
			stmt.setString(1, s.getName());
			list = new ArrayList<Question>();
			rs = stmt.executeQuery();
			while (rs.next())
			{
				q = new Question();
				q.setText(rs.getString(1));
				q.setId(rs.getString(2));
				list.add(q);
			}
			SystemObject sys = new SystemObject();
			sys.setMode(SysMode.OK);
			sys.setObj(list);
			return sys;
			
		} catch (SQLException e) {EchoServer.display("Method getQuestionsbysid: "+e.getMessage());}
		return null;
	}

	public SystemObject getExam()
	{
		//Object[] result = new Object[2];
		List<PracticalExam> list;
		PracticalExam exam;
		Statement st;
		String Query = "SELECT * from practicalexam";
		ResultSet rs;
		try {
			list = new ArrayList<PracticalExam>();
			st = conn.createStatement();
			rs = st.executeQuery(Query);
			while (rs.next())
			{
				exam = new PracticalExam();
				exam.setExamID(rs.getString(1));
				exam.setCourse(rs.getString(2));
				list.add(exam);
			}
			SystemObject obj = new SystemObject();
			obj.setMode(SysMode.OK);
			obj.setObj(list);
			return obj;
		} catch (SQLException e) {EchoServer.display("Method getLogin: "+e.getMessage());}
		return null;
	}

	
	public SystemObject getSubject(User usr)
	{
		List<Subject> list;
		Subject s;
		PreparedStatement st;
		ResultSet rs;
		String Query = "SELECT DISTINCT name FROM project.teachersubject WHERE teacherID=? "; //SELECT * from subject
		try {
			st = conn.prepareStatement(Query);
			st.setString(1, usr.getUserID());
			list = new ArrayList<Subject>();
			rs = st.executeQuery();
			
			while (rs.next())
			{
				s = new Subject();
				s.setName(rs.getString(1));
				list.add(s);
			}
		SystemObject obj = new SystemObject();
		obj.setMode(SysMode.OK);
		obj.setObj(list);
		return obj;

	} catch (SQLException e) {EchoServer.display("Method Get Subject Failed: "+e.getMessage());}
	return null;
	}
	

	/**
	 * This method is used to log the current user out of the system
	 * Turning his/her isOnline field in DB to 0
	 * @param user the user to log out
	 * @return OK or NOT OK - Mode
	 */
	public SysMode LogOut(User user)
	{
		String Query = "Update Users SET isOnline = 0 WHERE userID = ?";
		PreparedStatement pstmt;

		try
		{
			pstmt = conn.prepareStatement(Query);
			pstmt.setString(1, user.getUserID());
			System.out.println(Query); ///////
			if (0 == pstmt.executeUpdate())
				return SysMode.NOT_OK;
			return SysMode.OK;
		}
		catch(Exception ex){return SysMode.NOT_OK;}
	}
	public int AddExam(PracticalExam Exam)
	{
		
		PreparedStatement pt;
		
		int rs;
		String Query = "INSERT INTO practicalexam (examID, Date , subE , course , duration ) Values ";
		Query+= "('";
		Query+=(Exam.getExamID()+ "','");
		Query+=(Exam.getDate()+ "','");
		Query+=(Exam.getSub()+ "','");
		Query+=(Exam.getCourse()+ "','");
		Query+=(Exam.getDuration()+"')");
		System.out.println(Query);
		try
		{
			pt = conn.prepareStatement(Query);
			rs=pt.executeUpdate();
			return rs=1;

		}
	 catch (SQLException e) {
		 EchoServer.display("Method InsertQuestion: "+e.getMessage());
		 }
	 return rs=0;
}
	/**
	 * Change Parent Block
	 * @return object of parent
	 */
	public SystemObject changParaentBlock(Object obj)
	{
		int flag = 0;
		isRestricted pi = new isRestricted();
		PreparedStatement pstmt;
		ResultSet rs;
		String query = new String("SELECT * FROM project.restricted WHERE studentID = ? ");
		String query1 = new String("UPDATE project.restricted SET isRestricted = ? WHERE studentID = ?");
		SystemObject result = new SystemObject();
		int rs1;
		pi = (isRestricted)obj;
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, pi.getUserID());
			rs = pstmt.executeQuery();	
			while(rs.next())
			{
				flag = 1;
				pi.setUsrId(rs.getString(1));
				pi.setRestricted(rs.getInt(3));
				if(pi.isRestricted() == 0)//if student is unBlock
					pi.setRestricted(1);//Block him
				else//if student is Block
					pi.setRestricted(0);//unBlock him

				//update change
				pstmt = conn.prepareStatement(query1);
				pstmt.setInt(1, pi.isRestricted());
				pstmt.setString(2, pi.getUserID());
				rs1 = pstmt.executeUpdate();

				//save message for client
				if( rs1 > 0)
				{
					if(pi.isRestricted() == 1)
					{
						result.setMode(SysMode.OK);
						result.setObj(new String("Exam BLOCKED!"));
					}
					else
					{
						result.setMode(SysMode.OK);
						result.setObj(new String("Exam UNBLOCKED!"));
					}	
				}
				else
				{
					result.setMode(SysMode.NOT_OK);
					result.setObj(new String("PROBLEM IN QUERY!!"));
				}
			}
			if(flag == 0 )
			{
				result.setMode(SysMode.NOT_OK);
				result.setObj(new String("Parent Id = " +pi.getUserID()+" DONT EXIST"));
			}
			return result;
		}
		catch(Exception ex) 
		{
			return (new SystemObject(new String("Exception, check with system Admin"),SysMode.FAIL));
		}
	}
	

}